using System;
using System.Web.Mvc;

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace AJAX.Controllers {
  public class ServiceController : Controller {
    static bool isLeapYear(int year) {
      if ((year % 4) != 0) return false;
      if ((year % 100) != 0) return true;
      if ((year % 400) != 0) return false;
      return true;
    }
    public string GetDays(int year, string sMonth) {
      string result = "<select id='Days' name='Days'>";
      int n = 31;
      switch (sMonth) {
        case "APR":
        case "JUN":
        case "SEP":
        case "NOV":
          n = 30;
          break;
        case "FEB":
          n = isLeapYear(year) ? 29 : 28;
          break;
      }
      for (int d = 1; d <= n; d++) {
        result += "<option>" + d + "</option>";
      }
      return result + "</select>";
    }

    //
    // GET: /Service/

    public ActionResult Index() {
      return View();
    }
    public string Countries() {
      uint count = 0;
      string result = "";
      SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
      SqlCommand cmd = new SqlCommand("SELECT [Name] FROM Country", conn);
      try {
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read()) {
          count++;
          result += "<option>" + dr["Name"] + "</option>";
        }
        dr.Close();
      } catch (Exception) {
      } finally {
        if (conn.State == ConnectionState.Open) conn.Close();
      }
      return (count == 0) ? "" : "<select id='Country' name='Country' onchange='loadStates()'><option>--Select Country--</option>" + result + "</select>";
    }
    public string States(string countryName, string useLocalName = "no") {
      uint count = 0;
      string result = "<select id='State' name='State'>";
      SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
      SqlCommand cmd = new SqlCommand(
        "SELECT s.Name, COALESCE(s.LocalName,s.Name) [LocalName] FROM State s JOIN Country c ON s.Country=c.Code WHERE c.Name='" + 
          countryName + "'", conn);
      try {
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read()) {
          count++;
          string name = dr[(useLocalName == "yes") ? "LocalName":"Name"].ToString();
          result += "<option>" + name + "</option>";
        }
        dr.Close();
      } catch (Exception ex) {
        //return ex.Message;
      } finally {
        if (conn.State == ConnectionState.Open) conn.Close();
      }
      return (count > 0) ? (result + "</select>") : "";
    }

    //public string States(string countryName, string useLocalName = "no") {
    //  uint count = 0;
    //  string result = "<select id='State' name='State'>";
    //  SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
    //  SqlCommand cmd = new SqlCommand("SELECT [Code] FROM Country WHERE [Name]='" + countryName + "'", conn);
    //  try {
    //    conn.Open();
    //    SqlDataReader dr = cmd.ExecuteReader();
    //    if (dr.Read()) {
    //      string countryCode = dr["Code"].ToString();
    //      dr.Close();
    //      cmd.CommandText = "SELECT [Name],[LocalName] FROM State WHERE [Country]='" + countryCode + "'";
    //      dr = cmd.ExecuteReader();
    //      while (dr.Read()) {
    //        count++;
    //        string name = dr["Name"].ToString();
    //        if (useLocalName == "yes") {
    //          string localName = (string)dr["LocalName"].ToString().Trim();
    //          if (localName.Length > 0) name = localName;
    //        }
    //        result += "<option>" + name + "</option>";
    //      }
    //    }
    //    dr.Close();
    //  } catch (Exception) {
    //  } finally {
    //    if (conn.State == ConnectionState.Open) conn.Close();
    //  }
    //  return (count > 0) ? (result + "</select>") : "";
    //}

    public string LoadMap(string countryName) {
      string result = "";
      SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
      SqlCommand cmd = new SqlCommand(
        "SELECT Code FROM Country WHERE Name='" +countryName + "'", conn);
      try {
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read()) {
          string code = dr.GetString(0);
          dr.Close();
          cmd.CommandText = "ReadMap";
          cmd.CommandType = CommandType.StoredProcedure;

          cmd.Parameters.Add("@code", SqlDbType.Char, 2);
          cmd.Parameters["@code"].Direction = ParameterDirection.Input;
          cmd.Parameters["@code"].Value = code;

          cmd.Parameters.Add("@map", SqlDbType.NVarChar,int.MaxValue);
          cmd.Parameters["@map"].Direction = ParameterDirection.Output;

          cmd.ExecuteNonQuery();
          result = cmd.Parameters["@map"].Value.ToString();
        }
      } catch (Exception ex) {
        //return ex.Message;
      } finally {
        if (conn.State == ConnectionState.Open) conn.Close();
      }
      return result;
    }

    public string CheckUser(string userName) {
      string result = "";
      SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
      SqlCommand cmd = new SqlCommand("SELECT [Password] FROM Member WHERE [Name]='" + userName + "'", conn);
      try {
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read()) {
          result = dr["Password"].ToString();
        }
        dr.Close();
      } catch (Exception) {
      } finally {
        if (conn.State == ConnectionState.Open) conn.Close();
      }
      System.Threading.Thread.Sleep(5000);
      return result;
    }
  }
}